# -*- coding: utf-8 -*-
"""
Chapter 5: Database Integration with SQLAlchemy

This demo covers:
- Flask-SQLAlchemy integration
- Model definition with relationships
- CRUD operations (Create, Read, Update, Delete)
- Querying with SQLAlchemy ORM
- Pagination
- Database migrations (conceptual)
"""

import os
import secrets

from flask import Flask, render_template, request, redirect, url_for, flash
from sqlalchemy import desc

from models import db, User, Post

app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', secrets.token_hex(16))

# Database configuration
basedir = os.path.abspath(os.path.dirname(__file__))
app.config['SQLALCHEMY_DATABASE_URI'] = os.getenv('DATABASE_URL', f'sqlite:///{os.path.join(basedir, "app.db")}')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False

# Initialize SQLAlchemy with the app
db.init_app(app)

# Create tables
with app.app_context():
    db.create_all()

    # Add sample data if tables are empty
    if User.query.count() == 0 and Post.query.count() == 0:
        # Create sample users
        user1 = User(username='alice', email='alice@example.com')
        user2 = User(username='bob', email='bob@example.com')
        db.session.add_all([user1, user2])
        db.session.commit()

        # Create sample posts
        post1 = Post(title='First Post', body='This is the first post.', author=user1)
        post2 = Post(title='Second Post', body='This is the second post.', author=user1)
        post3 = Post(title='Third Post', body='This is the third post.', author=user2)
        db.session.add_all([post1, post2, post3])
        db.session.commit()


# Routes
@app.route('/')
def index():
    """Home page with database statistics."""
    users_count = User.query.count()
    posts_count = Post.query.count()
    db_path = app.config['SQLALCHEMY_DATABASE_URI']

    return render_template('index.html',
                           users_count=users_count,
                           posts_count=posts_count,
                           db_path=db_path)


@app.route('/users')
def users():
    """Display all users."""
    users = User.query.order_by(User.member_since.desc()).all()
    return render_template('users.html', users=users)


@app.route('/posts')
def posts():
    """Display all posts with pagination."""
    page = request.args.get('page', 1, type=int)
    posts = Post.query.order_by(desc(Post.timestamp)).paginate(
        page=page, per_page=5, error_out=False)
    authors_count = User.query.count()
    return render_template('posts.html', posts=posts.items, pagination=posts, authors_count=authors_count)


@app.route('/user/<int:user_id>/posts')
def user_posts(user_id):
    """Display posts by a specific user."""
    user = User.query.get_or_404(user_id)
    posts = Post.query.filter_by(user_id=user_id).order_by(desc(Post.timestamp)).all()
    return render_template('user_posts.html', user=user, posts=posts)


@app.route('/create_user', methods=['GET', 'POST'])
def create_user():
    """Create a new user."""
    errors = {}

    if request.method == 'POST':
        username = request.form.get('username', '').strip()
        email = request.form.get('email', '').strip()
        password = request.form.get('password', '')

        # Validate form data
        errors['username'] = User.validate_username(username)
        errors['email'] = User.validate_email(email)
        if not password:
            errors['password'] = "Password is required"
        elif len(password) < 6:
            errors['password'] = "Password must be at least 6 characters"

        # Check if username or email already exists
        if not errors.get('username') and User.query.filter_by(username=username).first():
            errors['username'] = "Username already exists"

        if not errors.get('email') and User.query.filter_by(email=email).first():
            errors['email'] = "Email already exists"

        # If no errors, create the user
        if not any(errors.values()):
            user = User(username=username, email=email)
            # In a real app, you would hash the password here
            db.session.add(user)
            db.session.commit()
            flash(f'User {username} created successfully!', 'success')
            return redirect(url_for('users'))
        else:
            flash('Please correct the errors below.', 'error')

    return render_template('create_user.html', errors=errors)


@app.route('/create_post', methods=['GET', 'POST'])
def create_post():
    """Create a new post."""
    errors = {}
    users = User.query.order_by(User.username).all()

    if request.method == 'POST':
        author_id = request.form.get('author_id', type=int)
        title = request.form.get('title', '').strip()
        body = request.form.get('body', '').strip()

        # Validate form data
        if not author_id:
            errors['author_id'] = "Please select an author"
        elif not User.query.get(author_id):
            errors['author_id'] = "Selected author does not exist"

        errors['title'] = Post.validate_title(title)
        errors['body'] = Post.validate_body(body)

        # If no errors, create the post
        if not any(errors.values()):
            post = Post(title=title, body=body, user_id=author_id)
            db.session.add(post)
            db.session.commit()
            flash(f'Post "{title}" created successfully!', 'success')
            return redirect(url_for('posts'))
        else:
            flash('Please correct the errors below.', 'error')

    return render_template('create_post.html', users=users, errors=errors)


@app.route('/edit_user/<int:user_id>', methods=['GET', 'POST'])
def edit_user(user_id):
    """Edit an existing user."""
    user = User.query.get_or_404(user_id)
    errors = {}

    if request.method == 'POST':
        username = request.form.get('username', '').strip()
        email = request.form.get('email', '').strip()

        # Validate form data
        errors['username'] = User.validate_username(username)
        errors['email'] = User.validate_email(email)

        # Check if username or email already exists (excluding current user)
        if not errors.get('username') and User.query.filter(User.username == username, User.id != user_id).first():
            errors['username'] = "Username already exists"

        if not errors.get('email') and User.query.filter(User.email == email, User.id != user_id).first():
            errors['email'] = "Email already exists"

        # If no errors, update the user
        if not any(errors.values()):
            user.username = username
            user.email = email
            db.session.commit()
            flash(f'User {username} updated successfully!', 'success')
            return redirect(url_for('users'))
        else:
            flash('Please correct the errors below.', 'error')

    return render_template('edit_user.html', user=user, errors=errors)


@app.route('/edit_post/<int:post_id>', methods=['GET', 'POST'])
def edit_post(post_id):
    """Edit an existing post."""
    post = Post.query.get_or_404(post_id)
    errors = {}

    if request.method == 'POST':
        title = request.form.get('title', '').strip()
        body = request.form.get('body', '').strip()

        # Validate form data
        errors['title'] = Post.validate_title(title)
        errors['body'] = Post.validate_body(body)

        # If no errors, update the post
        if not any(errors.values()):
            post.title = title
            post.body = body
            db.session.commit()
            flash(f'Post "{title}" updated successfully!', 'success')
            return redirect(url_for('posts'))
        else:
            flash('Please correct the errors below.', 'error')

    return render_template('edit_post.html', post=post, errors=errors)


@app.route('/delete_user/<int:user_id>', methods=['POST'])
def delete_user(user_id):
    """Delete a user."""
    user = User.query.get_or_404(user_id)
    username = user.username
    db.session.delete(user)
    db.session.commit()
    flash(f'User {username} deleted successfully!', 'success')
    return redirect(url_for('users'))


@app.route('/delete_post/<int:post_id>', methods=['POST'])
def delete_post(post_id):
    """Delete a post."""
    post = Post.query.get_or_404(post_id)
    title = post.title
    db.session.delete(post)
    db.session.commit()
    flash(f'Post "{title}" deleted successfully!', 'success')
    return redirect(url_for('posts'))


# Error handlers
@app.errorhandler(404)
def not_found(error):
    """Handle 404 errors."""
    flash('The requested resource was not found.', 'error')
    return redirect(url_for('index'))


if __name__ == '__main__':
    app.run(
        host=os.getenv('FLASK_HOST', '127.0.0.1'),
        port=int(os.getenv('FLASK_PORT', 5000)),
        debug=os.getenv('FLASK_DEBUG', 'True').lower() == 'true'
    )
